import pymysql
import openpyxl
import time


# 获取数据库的连接
def connection():
    conn = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        database='boke'
    )
    return conn

# 获取系统的格式化时间
def getTime():
    return time.asctime()

# 向数据库中插入评论， 两个形参，第一个是id， 第二个是评论内容
def insertComment(idcomment, comment):
    conn = connection()
    cur = conn.cursor()
    sql = "INSERT INTO COMMENT VALUES('{}', '{}', '{}')".format(idcomment, comment, getTime())
    cur.execute(sql)
    conn.commit()

# 从excel中获取排行榜，并将其导入数据库中，注意：excel存放文件为data.xlsx
def insertExcel():
    conn = connection()
    cur = conn.cursor()
    data = openpyxl.load_workbook(r'data.xlsx')
    data1 = data.active
    value = list(data1.values)
    for i in value[1:]:
        sql = "INSERT INTO LIST VALUES('{}', '{}', '{}')".format(i[0], i[1], i[2])
        cur.execute(sql)
    conn.commit()


# 向数据库中存放博客的内容
def insertBoke(title, article):
    conn = connection()
    cur = conn.cursor()
    sql = "INSERT INTO NEW_TABLE VALUES('{}', '{}')".format(title, article)
    cur.execute(sql)
    conn.commit()

